package com.api.gbhg.daiyanlu.Dao;

import com.google.common.base.Strings;
import weaver.conn.RecordSet;

/**
 *预算执行情况表:预算年度,单位,预算项目,预算总额,审批中费用,实际发生数,可用额度,执行率(%)
 * 查询条件:年度,单位,项目
 */
public class BudgetaryPerformanceDao {
    public static RecordSet getBpDao(Integer pageNum, Integer pageSize,int nd,String dw,String xm) {
        RecordSet rs = new RecordSet();
        StringBuilder sql = new StringBuilder();
        if(dw.equals("2")){
            if(!Strings.isNullOrEmpty((xm))){
                sql.append("select jv.ysnd,jv.sbdw,jv.xmmin,jv.yszje,j.dj,j.syed from uf_jingfei_version jv INNER join uf_jingfei j\n" +
                        "on j.requestId = jv.requestId where jv.lcztz=3 and jv.ysnd=" + nd + " and jv.lcbj="+xm+" ");
            }else {
                sql.append("select jv.ysnd,jv.sbdw,jv.xmmin,jv.yszje,j.dj,j.syed from uf_jingfei_version jv INNER join uf_jingfei j\n" +
                        "on j.requestId = jv.requestId where jv.lcztz=3 and jv.ysnd=" + nd + " ");
            }
        }else {
            if(!Strings.isNullOrEmpty((xm))){
                sql.append("select jv.ysnd,jv.sbdw,jv.xmmin,jv.yszje,j.dj,j.syed from uf_jingfei_version jv INNER join uf_jingfei j\n" +
                        "on j.requestId = jv.requestId where jv.lcztz=3 and jv.ysnd=" + nd + " and jv.sbdw in ("+dw+") and jv.lcbj="+xm+" ");
            }else {
                sql.append("select jv.ysnd,jv.sbdw,jv.xmmin,jv.yszje,j.dj,j.syed from uf_jingfei_version jv INNER join uf_jingfei j\n" +
                        "on j.requestId = jv.requestId where jv.lcztz=3 and jv.ysnd=" + nd + " and jv.sbdw in ("+dw+") ");
            }
        }

        pageNum = (pageNum - 1) * pageSize;
        if ("sqlserver".equals(rs.getDBType())) {
            sql.append(" order by jv.requestId offset " + pageNum + " rows fetch next " + pageSize + " rows only");
        } else if ("mysql".equals(rs.getDBType())) {
            sql.append(" LIMIT " + pageNum + "," + pageSize + "");
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    public static RecordSet getBpMax(int nd, String dw, String xm) {
        RecordSet rs = new RecordSet();
        StringBuilder sql = new StringBuilder();
        if(dw.equals("2")){
            if(!Strings.isNullOrEmpty((xm))) {
                sql.append("select sum(j.syed)syed,sum(j.dj)dj,sum(jv.yszje)yszje from uf_jingfei_version jv INNER join uf_jingfei j\n" +
                        "on j.requestId = jv.requestId where jv.lcztz=3 and jv.ysnd=" + nd + " and jv.lcbj=" + xm + " ");
            }else {
                sql.append("select sum(j.syed)syed,sum(j.dj)dj,sum(jv.yszje)yszje from uf_jingfei_version jv INNER join uf_jingfei j\n" +
                        "on j.requestId = jv.requestId where jv.lcztz=3 and jv.ysnd=" + nd + "  ");
            }
        }else {
            if(!Strings.isNullOrEmpty((xm))) {
                sql.append("select sum(j.syed)syed,sum(j.dj)dj,sum(jv.yszje)yszje from uf_jingfei_version jv INNER join uf_jingfei j\n" +
                        "on j.requestId = jv.requestId where jv.lcztz=3 and jv.ysnd=" + nd + " and jv.sbdw in ("+dw+") and jv.lcbj=" + xm + " ");
            }else {
                sql.append("select sum(j.syed)syed,sum(j.dj)dj,sum(jv.yszje)yszje from uf_jingfei_version jv INNER join uf_jingfei j\n" +
                        "on j.requestId = jv.requestId where jv.lcztz=3 and jv.ysnd=" + nd + " and jv.sbdw in ("+dw+")  ");
            }
        }
        rs.executeQuery(sql.toString());
        return rs;
    }

    /**
     * 预算执行情况表总条数
     * @return
     */
    public int getBpCount(int nd,String dw,String xm){
        RecordSet rs=new RecordSet();
        String sql ;
        if(dw.equals("2")){
            if(!Strings.isNullOrEmpty((xm))) {
                sql = "select count(1)AS num from uf_jingfei j INNER join uf_jingfei_version jv on j.requestId = jv.requestId where lcztz=3 and jv.ysnd=" + nd + " and jv.lcbj=" + xm + " ";
            }else {
                sql = "select count(1)AS num from uf_jingfei j INNER join uf_jingfei_version jv on j.requestId = jv.requestId where lcztz=3 and jv.ysnd=" + nd + "  ";
            }
        }else {
            if(!Strings.isNullOrEmpty((xm))) {
                sql = "select count(1)AS num from uf_jingfei j INNER join uf_jingfei_version jv on j.requestId = jv.requestId where lcztz=3 and jv.ysnd=" + nd + " and jv.sbdw in ("+dw+") and jv.lcbj=" + xm + " ";
            }else {
                sql = "select count(1)AS num from uf_jingfei j INNER join uf_jingfei_version jv on j.requestId = jv.requestId where lcztz=3 and jv.ysnd=" + nd + " and jv.sbdw in ("+dw+")  ";
            }
        }
        rs.executeQuery(sql);
        rs.next();
        return rs.getInt("num");
    }
}
